Google Colab上でSQLを使って基本統計量を確認してみた

みなさん、こんにちは。

クルトンです！

今回は、前回ブログの流れから、「基本統計量をSQLで把握するのはどうしたら良いのか」という疑問を持ったので、確かめる意味でやってみました。 基本統計量とは、本ブログで以下のものを指します。

• 最大値
• 最小値
• 平均値
• 中央値
• 最頻値
• 分散
• 標準偏差

SQLの実行環境を準備

前回ブログと同じ内容ですので、知っている方は飛ばしていただいて大丈夫です。

実行環境はGoogle Colabで、ランタイムはCPUです。

まずは必要なモジュールとデータをダウンロードします。

# IPython用のモジュールをダウンロード
!pip install jupysql duckdb-engine

# データ（csvファイル）のダウンロード
!wget https://raw.githubusercontent.com/mwaskom/seaborn-data/master/penguins.csv

ここでダウンロードしたpenguins.csvは、ペンギンの種類やどこの島に生息しているかなどのデータが記録されているテーブルデータになります。

次に、Colab上でSQLが使えるように、データ読み込みとDBの設定をします。

%load_ext sql

%sql duckdb://

これで、データの用意とSQLクエリが使えるようになりました。

Colabのセル上でSQLを実行するには、 %%sqlと書いて、その直下にSQL文を書きます。 例えば以下のように書きます。

%%sql
SELECT
  *
FROM
  penguins.csv

また、以下のように書いて、実行結果を変数に入れることも可能です。

%%sql 変数名 <<

実際に変数に入れてデータを確認する例は次のようになります。

%%sql all_result <<
SELECT
  *
FROM
  penguins.csv

基本統計量を確認してみた

本ブログ冒頭でも書いたように、以下の内容を確認していきます。

• 最大値
• 最小値
• 平均値
• 中央値
• 最頻値
• 分散
• 標準偏差

確認する対象としては、speciesカラムにある以下のペンギンの種別ごとに値を確認していきます。

• Adelie アデリーペンギン
• Chinstrap ヒゲペンギン
• Gentoo ジェンツーペンギン

最大値

MAX関数を使う事で、最大値を確認していきます。 NULLが帰ってきた時のためにCOALESCE関数を使って、NULLが帰ってきた場合は0を表示するようにしています。

%%sql
SELECT
  species
  , COALESCE(MAX(bill_length_mm), 0) as max_length
  , COALESCE(MAX(bill_depth_mm), 0) as max_depth
  , COALESCE(MAX(flipper_length_mm), 0) as max_flipper_length
  , COALESCE(MAX(body_mass_g), 0) as max_body_mass
FROM
  penguins.csv
GROUP BY
  species

実行結果は次のようになります。

species max_length  max_depth   max_flipper_length  max_body_mass
Adelie  46.0    21.5    210 4775
Chinstrap   58.0    20.8    212 4800
Gentoo  59.6    17.3    231 6300

最小値

MIN関数を使うことで、最小値を確認していきます。 最大値の時と同じく、NULLが帰ってきた時のためにCOALESCE関数を使って、NULLが帰ってきた場合は0を表示するようにしています。

SELECT
  species
  , COALESCE(MIN(bill_length_mm), 0) as min_length
  , COALESCE(MIN(bill_depth_mm), 0) as min_depth
  , COALESCE(MIN(flipper_length_mm), 0) as min_flipper_length
  , COALESCE(MIN(body_mass_g), 0) as min_body_mass
FROM
  penguins.csv
GROUP BY
  species

実行結果は次のようになります。

species min_length  min_depth   min_flipper_length  min_body_mass
Adelie  32.1    15.5    172 2850
Chinstrap   40.9    16.4    178 2700
Gentoo  40.9    13.1    203 3950

平均値

AVG関数を使って、平均値を確認していきます。 最大値の時と同じく、NULLが帰ってきた時のためにCOALESCE関数を使って、NULLが帰ってきた場合は0を表示するようにしています。

SELECT
  species
  , COALESCE(AVG(bill_length_mm), 0) as avg_length
  , COALESCE(AVG(bill_depth_mm), 0) as avg_depth
  , COALESCE(AVG(flipper_length_mm), 0) as avg_flipper_length
  , COALESCE(AVG(body_mass_g), 0) as avg_body_mass
FROM
  penguins.csv
GROUP BY
  species

実行結果は次のようになります。

species avg_length  avg_depth   avg_flipper_length  avg_body_mass
Adelie  38.79139072847684   18.346357615894032  189.95364238410596  3700.662251655629
Chinstrap   48.83382352941177   18.420588235294115  195.8235294117647   3733.0882352941176
Gentoo  47.504878048780476  14.982113821138206  217.1869918699187   5076.016260162602

中央値

MEDIAN関数を使って、中央値を確認していきます。 最大値の時と同じく、NULLが帰ってきた時のためにCOALESCE関数を使って、NULLが帰ってきた場合は0を表示するようにしています。

SELECT
  species
  , COALESCE(MEDIAN(bill_length_mm), 0) as med_length
  , COALESCE(MEDIAN(bill_depth_mm), 0) as med_depth
  , COALESCE(MEDIAN(flipper_length_mm), 0) as med_flipper_length
  , COALESCE(MEDIAN(body_mass_g), 0) as med_body_mass
FROM
  penguins.csv
GROUP BY
  species

実行結果は次のようになります。

species med_length  med_depth   med_flipper_length  med_body_mass
Adelie  38.8    18.4    190.0   3700.0
Chinstrap   49.55   18.45   196.0   3700.0
Gentoo  47.3    15.0    216.0   5000.0

最頻値

最頻値については定義されている関数がないため、自前で実装しました。

%%sql
WITH cnt_tbl AS(
  SELECT
    species
    , bill_length_mm
    , COUNT(*) AS cnt
  FROM
    penguins.csv
  WHERE
    species='Gentoo'
  GROUP BY
    bill_length_mm, species
)
, max_cnt_tbl AS(
  SELECT
    MAX(cnt) as max_cnt
  FROM
    cnt_tbl
)
SELECT
  species
  , bill_length_mm
  , max_cnt as mode
FROM
  cnt_tbl, max_cnt_tbl
WHERE
  cnt>=max_cnt

GROUP BY句を使う都合上、全てのカラムでなく、特定のカラムの最頻値を計算しています。 また、ペンギンの種別ごとに計算する一例として今回はWHERE species='Gentoo'とGentooを指定しています。他種別を確認したい場合はここをGentooの部分を別の種別名に変更してください。

最頻値計算をするカラムを変更したい場合は、cnt_tblテーブルでSELECT文の対象にしているカラム名(ここではbill_length_mm)とGROUP BY句のカラム名(ここではbill_length_mm)を確認したいカラム名に変更すると良いです。

実行結果は次のようになります。4が最頻値の値で、複数該当は全部表示されます。

species bill_length_mm  mode
Gentoo  50.0    4
Gentoo  46.5    4
Gentoo  45.5    4
Gentoo  46.2    4
Gentoo  45.2    4

分散

VARIANCE関数を使って、分散を確認していきます。 最大値の時と同じく、NULLが帰ってきた時のためにCOALESCE関数を使って、NULLが帰ってきた場合は0を表示するようにしています。

%%sql
SELECT
  species
  , COALESCE(VARIANCE(bill_length_mm), 0) as var_length
  , COALESCE(VARIANCE(bill_depth_mm), 0) as var_depth
  , COALESCE(VARIANCE(flipper_length_mm), 0) as var_flipper_length
  , COALESCE(VARIANCE(body_mass_g), 0) as var_body_mass
FROM
  penguins.csv
GROUP BY
  species

実行結果は次のようになります。

species var_length  var_depth   var_flipper_length  var_body_mass
Adelie  7.09372538631347    1.4802366445916129  42.76450331125821   210282.89183222956
Chinstrap   11.150629938542583  1.289122036874451   50.86391571553991   147713.45478489902
Gentoo  9.497844862055171   0.9627922164467538  42.05491136878581   254133.18006130887

標準偏差

STDDEV関数を使って、標準偏差を確認していきます。 最大値の時と同じく、NULLが帰ってきた時のためにCOALESCE関数を使って、NULLが帰ってきた場合は0を表示するようにしています。

%%sql
SELECT
  species
  , COALESCE(STDDEV(bill_length_mm), 0) as var_length
  , COALESCE(STDDEV(bill_depth_mm), 0) as var_depth
  , COALESCE(STDDEV(flipper_length_mm), 0) as var_flipper_length
  , COALESCE(STDDEV(body_mass_g), 0) as var_body_mass
FROM
  penguins.csv
GROUP BY
  species

実行結果は次のようになります。

species var_length  var_depth   var_flipper_length  var_body_mass
Adelie  2.6634048483686197  1.216649762500126   6.539457417191293   458.56612591013476
Chinstrap   3.339255895935887   1.1353951016604091  7.1318942585781455  384.3350813871914
Gentoo  3.081857372114286   0.9812197595068873  6.484975818673946   504.1162366570917

終わりに

今回は基本統計量をSQLのみを使って、基本統計量を確認してみました。 思った以上に必要な関数が定義されているため、簡単に確認できました。

最頻値についてだけは他にもっと良い実装方法がないか考えたいところです。

今回はここまで。

それでは、また！